const { Sequelize, DataTypes, Op, where } = require('sequelize')
var path = require('path');
var storagePath = path.resolve(__dirname, '../files/.sqlite3.db')
const sequelize = new Sequelize({
    dialect: 'sqlite',
    storage: storagePath,
    logging: false
});

var config = sequelize.define('config', {
    // addPort: {
    //     type: DataTypes.INTEGER, allowNull: false, validate: {
    //         isValidate(value) {
    //             var arr = [17, 27, 22, 5, 6, 13, 19, 26, 18, 23, 24, 25]
    //             if (!arr.includes(value)) {
    //                 throw new Error('Gpio input port num is not validate,please choose 17, 27, 22, 5, 6, 13, 19, 26, 18, 23, 24, 25');
    //             }
    //         }
    //     }
    // },
    // deletePort: {
    //     type: DataTypes.INTEGER, allowNull: false, validate: {
    //         isValidate(value) {
    //             var arr = [17, 27, 22, 5, 6, 13, 19, 26, 18, 23, 24, 25]
    //             if (!arr.includes(value)) {
    //                 throw new Error('Gpio intput port num is not validate,please choose 17, 27, 22, 5, 6, 13, 19, 26, 18, 23, 24, 25');
    //             }
    //         }
    //     }
    // },
    // triggerPort: {
    //     type: DataTypes.INTEGER, allowNull: false, validate: {
    //         isValidate(value) {
    //             var arr = [12, 16, 20, 21]
    //             if (!arr.includes(value)) {
    //                 throw new Error('Gpio out port num is not validate,please choose 12, 16, 20, 21');
    //             }
    //         }
    //     }
    // },
    addPort: { type: DataTypes.INTEGER, allowNull: false },
    deletePort: { type: DataTypes.INTEGER, allowNull: false },
    triggerPort: { type: DataTypes.INTEGER, allowNull: false },
    serialPath: { type: DataTypes.STRING, allowNull: false },
    frame: { type: DataTypes.STRING, allowNull: false },

}, {
    freezeTableName: true
})

var normalTable = sequelize.define('normalTable', {
    ch1value: { type: DataTypes.STRING },
    ch2value: { type: DataTypes.STRING },
    ch3value: { type: DataTypes.STRING },
    ch4value: { type: DataTypes.STRING },
    ch5value: { type: DataTypes.STRING },
    ch6value: { type: DataTypes.STRING },
    ch7value: { type: DataTypes.STRING },
    ch8value: { type: DataTypes.STRING },
    totalJudge: { type: DataTypes.STRING },
    ch1judge: { type: DataTypes.STRING },
    ch2judge: { type: DataTypes.STRING },
    ch3judge: { type: DataTypes.STRING },
    ch4judge: { type: DataTypes.STRING },
    ch5judge: { type: DataTypes.STRING },
    ch6judge: { type: DataTypes.STRING },
    ch7judge: { type: DataTypes.STRING },
    ch8judge: { type: DataTypes.STRING },
    type: { type: DataTypes.STRING },
    date: { type: DataTypes.DATEONLY },
    time: { type: DataTypes.STRING },
    timeStamp: { type: DataTypes.STRING }
}, {
    freezeTableName: true
})

var vatTable = sequelize.define('vatTable', {
    type: { type: DataTypes.STRING },
    totalJudge: { type: DataTypes.STRING },
    innerDiameter: { type: DataTypes.FLOAT },
    innerDiameterLevel: { type: DataTypes.STRING },
    grooveWidth: { type: DataTypes.FLOAT },
    grooveWidthLevel: { type: DataTypes.STRING },
    height: { type: DataTypes.FLOAT },
    heightLevel: { type: DataTypes.STRING },
    timeStamp: { type: DataTypes.STRING }
}, {
    freezeTableName: true
})

var rollerTable = sequelize.define('rollerTable', {
    type: { type: DataTypes.STRING },
    totalJudge: { type: DataTypes.STRING },
    innerDiameter: { type: DataTypes.FLOAT },
    innerDiameterLevel: { type: DataTypes.STRING },
    wallThickness: { type: DataTypes.FLOAT },
    wallThicknessLevel: { type: DataTypes.STRING },
    height: { type: DataTypes.FLOAT },
    heightLevel: { type: DataTypes.STRING },
    timeStamp: { type: DataTypes.STRING }
}, {
    freezeTableName: true
})

var crankTable = sequelize.define('crankTable', {
    type: { type: DataTypes.STRING },
    longDiameter: { type: DataTypes.FLOAT },
    longDiameterLevel: { type: DataTypes.STRING },
    shortDiameter: { type: DataTypes.FLOAT },
    shortDiameterLevel: { type: DataTypes.STRING },
    topRS: { type: DataTypes.FLOAT },
    topRSLevel: { type: DataTypes.STRING },
    bottomRS: { type: DataTypes.FLOAT },
    bottomRSLevel: { type: DataTypes.STRING },
    timeStamp: { type: DataTypes.STRING }
}, {
    freezeTableName: true
})

var spcTable = sequelize.define('spcTable', {
    ch1value: { type: DataTypes.STRING },
    ch2value: { type: DataTypes.STRING },
    ch3value: { type: DataTypes.STRING },
    ch4value: { type: DataTypes.STRING },
    ch5value: { type: DataTypes.STRING },
    ch6value: { type: DataTypes.STRING },
    ch7value: { type: DataTypes.STRING },
    ch8value: { type: DataTypes.STRING },
    type: { type: DataTypes.STRING },
    zeroValue1: { type: DataTypes.FLOAT },
    zeroValue2: { type: DataTypes.FLOAT },
    zeroValue3: { type: DataTypes.FLOAT },
    zeroValue4: { type: DataTypes.FLOAT },
    zeroValue5: { type: DataTypes.FLOAT },
    zeroValue6: { type: DataTypes.FLOAT },
    zeroValue7: { type: DataTypes.FLOAT },
    zeroValue8: { type: DataTypes.FLOAT },
    gainValue1: { type: DataTypes.FLOAT },
    gainValue2: { type: DataTypes.FLOAT },
    gainValue3: { type: DataTypes.FLOAT },
    gainValue4: { type: DataTypes.FLOAT },
    gainValue5: { type: DataTypes.FLOAT },
    gainValue6: { type: DataTypes.FLOAT },
    gainValue7: { type: DataTypes.FLOAT },
    gainValue8: { type: DataTypes.FLOAT },
    checkCycle: { type: DataTypes.FLOAT },
    tolerance1: { type: DataTypes.FLOAT },
    tolerance2: { type: DataTypes.FLOAT },
    tolerance3: { type: DataTypes.FLOAT },
    tolerance4: { type: DataTypes.FLOAT },
    tolerance5: { type: DataTypes.FLOAT },
    tolerance6: { type: DataTypes.FLOAT },
    tolerance7: { type: DataTypes.FLOAT },
    tolerance8: { type: DataTypes.FLOAT },
    tolerance9: { type: DataTypes.FLOAT },
    tolerance10: { type: DataTypes.FLOAT },
    tolerance11: { type: DataTypes.FLOAT },
    tolerance12: { type: DataTypes.FLOAT },
    tolerance13: { type: DataTypes.FLOAT },
    tolerance14: { type: DataTypes.FLOAT },
    tolerance15: { type: DataTypes.FLOAT },
    tolerance16: { type: DataTypes.FLOAT },
    ch1Level: { type: DataTypes.STRING },
    ch1Leve2: { type: DataTypes.STRING },
    ch1Leve3: { type: DataTypes.STRING },
    ch1Leve4: { type: DataTypes.STRING },
    ch1Leve5: { type: DataTypes.STRING },
    ch1Leve6: { type: DataTypes.STRING },
    ch1Leve7: { type: DataTypes.STRING },
    ch1Leve8: { type: DataTypes.STRING },
    timeStamp: { type: DataTypes.STRING }
}, {
    freezeTableName: true
})

var serialTable = sequelize.define('serialTable', {
    serialPath: { type: DataTypes.STRING, allowNull: false },
    baudRate: {
        type: DataTypes.INTEGER, allowNull: false, validate: {
            isValidate(value) {
                var arr = [9600, 19200, 38400]
                if (!arr.includes(value)) {
                    throw new Error('baudRate is no validate,please choose 9600,19200,38400');
                }
            }
        },
        defaultValue: 9600
    },
    dataBits: { type: DataTypes.INTEGER, allowNull: false, validate: { is: /-?[7-8]\d*/ }, defaultValue: 8 },
    stopBits: { type: DataTypes.INTEGER, allowNull: false, validate: { is: { args: /-?[0-2]\d*/, msg: "请选择0，1，2" } }, defaultValue: 1 },
    parity: { type: DataTypes.STRING, allowNull: false, validate: { isIn: [['none', 'even', 'mark', 'odd', 'space']] }, defaultValue: 'none' },
}, {
    freezeTableName: true
})

var syncTables = (cb) => {
    sequelize.authenticate().then(() => {
        config.sync().then(async () => {
            if (await config.count() == 0)
                config.create({
                    addPort: 17,
                    deletePort: 27,
                    triggerPort: 16,
                    serialPath: '/dev/ttyUSB0',
                    frame: 'normal'
                })
        }).then(serialTable.sync().then(async () => {
            if (await serialTable.count() == 0)
                serialTable.create({
                    serialPath: '/dev/ttyUSB0',
                    baudRate: 9600,
                    dataBits: 8,
                    stopBits: 1,
                    parity: 'none',
                }).then(() => {
                    serialTable.create({
                        serialPath: '/dev/tty0',
                        baudRate: 9600,
                        dataBits: 8,
                        stopBits: 1,
                        parity: 'none',
                    })
                })
        })).then(normalTable.sync({ alter: false }))
            .then(vatTable.sync({ alter: false }))
            .then(rollerTable.sync({ alter: false }))
            .then(crankTable.sync({ alter: false }))
            .then(spcTable.sync({ alter: false }))
            .then(() => { if (cb) cb() })
    })
}


//获取配置信息
var getConfigInfo = (cb) => {
    config.findOne({ where: { id: 1 } }).then((data) => {
        cb(data.dataValues)
    })
}

//更新配置信息
var updateConfig = (values, options, cb) => {
    console.log(values)
    config.update(values, options).then((num) => {
        cb(num)
    })
}

//获取指定串口信息
var getSerialInfo = (path, cb) => {
    serialTable.findOrCreate({ where: { serialPath: path }, defaults: { serialPath: path } }).then((data) => {
        var [table, created] = data
        // console.log(table.dataValues)
        cb(table.dataValues)
    })
}

//更新串口信息
var updateSerialInfo = (values, options, cb) => {
    serialTable.update(values, options).then((num) => {
        cb(num)
    })
}

var insertRecord = (values, cb) => {
    getConfigInfo((infos) => {
        if (infos.frame == 'normal') {
            // console.log(values)
            normalTable.create(values).then((data) => {
                if (data) if (cb) cb()
            })
            // if (cb) cb(true)
        }
        else if (infos.frame == 'vat') {
            vatTable.create(values).then((data) => {
                if (data) if (cb) cb()
            })
        }
        else
            // console.log(infos)
            if (cb) cb({ msg: '该数据帧不存在' })
    })
    // if (cb) cb(false)
    //     vatTable.create(values).then((data) => {
    //     if (data) if (cb) cb()
    // })
    // if (cb) cb(true)
}

var deleteRecord = (params, cb) => {
    if (params.measureType == "normal") {
        normalTable.destroy({ where: { id: params.ids } }).then((num) => {
            if (num > 0) {
                if (cb) cb()
            }
            else if (cb) cb('未删除')
        })
    }
    if (params.measureType == "vat") {
        normalTable.destroy({ where: { id: params.ids } }).then((num) => {
            if (num > 0) {
                if (cb) cb()
            }
            else if (cb) cb('未删除')
        })
    }
}

var deleteLastRecord = (cb) => {
    getConfigInfo(async (infos) => {
        if (infos.frame == "normal") {
            var id = await normalTable.max('id')
            if (id)
                normalTable.destroy({
                    where: {
                        id: await normalTable.max('id')
                    }
                }).then((num) => {
                    if (num > 0) if (cb) cb()
                })
            else
                if (cb) cb({ msg: '表已空' })
        }
        else if (infos.frame == "vat") {
            var id = await vatTable.max('id')
            if (id)
                vatTable.destroy({
                    where: {
                        id: await vatTable.max('id')
                    }
                }).then((num) => {
                    if (num > 0) if (cb) cb()
                })
            else
                if (cb) cb({ msg: '表已空' })
        }
        else
            if (cb) cb({ msg: '数据库不存在该表' })
    })
}

var getRecord = (query, cb) => {
    var { measureType } = query
    if (measureType == 'normal') {
        var { page, perpage, order, order_by, fliter_by_judge, fliter_by_time, id, type } = query
        var values = {
            where: {}
        }
        if (page) values.offset = page
        if (perpage) values.limit = perpage
        if (order && order_by) values.order = [[order_by, order]]
        if (fliter_by_judge) values.where.totalJudge = { [Op.like]: `%${fliter_by_judge}` }
        if (fliter_by_time) {
            var stampArr = fliter_by_time.split('_')
            values.where.timeStamp = { [Op.between]: [stampArr[0], stampArr[1]] }
        }
        if (id) values.where.id = id
        if (type) values.where.type = type
        // console.log(values)
        normalTable.findAll(values).then(res => {
            // console.log(res)
            if (cb) cb(res)
        })
    } else if (measureType == 'vat') {
        var { page, perpage, order, order_by, fliter_by_judge, fliter_by_time, id, type } = query
        var values = {
            where: {}
        }
        if (page) values.offset = page
        if (perpage) values.limit = perpage
        if (order && order_by) values.order = [[order_by, order]]
        if (fliter_by_judge) values.where.totalJudge = { [Op.like]: `%${fliter_by_judge}` }
        if (fliter_by_time) {
            var stampArr = fliter_by_time.split('_')
            values.where.timeStamp = { [Op.between]: [stampArr[0], stampArr[1]] }
        }
        if (id) values.where.id = id
        if (type) values.where.type = type
        // console.log(values)
        vatTable.findAll(values).then(res => {
            // console.log(res)
            if (cb) cb(res)
        })
    }
    // else
}

module.exports = {
    syncTables,
    getConfigInfo,
    updateConfig,
    getSerialInfo,
    updateSerialInfo,
    insertRecord,
    deleteLastRecord,
    deleteRecord,
    getRecord,
}
